package com.estore.service.persitence;

import com.estore.service.persitence.core.MySQLDatabaseConnector;
import com.estore.service.model.User;
import com.estore.service.util.exception.EStoreException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDatabaseManager {

    private Connection conn;
    private ResultSet result;

    public boolean insertUser(User user) {
        boolean inserted = true;
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement insertUser = null;
        try {
            insertUser = conn.prepareStatement("insert into user set "
                    + "username=?, password=?, usertypeid=?, staffid=?");
            insertUser.setString(1, user.getUsername());
            insertUser.setString(2, user.getPassword());
            insertUser.setInt(3, user.getUserTypeId());
            insertUser.setInt(4, user.getStaffId());
            insertUser.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace(System.out);
            inserted = false;
        }
        return inserted;
    }

    public boolean updateUser(User user) {
        boolean inserted = true;
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement updateUser = null;
        try {
            updateUser = conn.prepareStatement("update user set "
                    + "username=?, password=?,  usertypeid=? where id=?");
            updateUser.setString(1, user.getUsername());
            updateUser.setString(2, user.getPassword());
            updateUser.setInt(3, user.getUserTypeId());
            updateUser.setInt(4, user.getStaffId());
            updateUser.setInt(5, user.getId());
            updateUser.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace(System.out);
            inserted = false;
        }
        return inserted;
    }

    public boolean authenticateUser(User user) {
        boolean authenticated = false;
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement authenticateUser = null;
        try {
            authenticateUser = conn.prepareStatement("select * from user where username=? and password=?");
            authenticateUser.setString(1, user.getUsername());
            authenticateUser.setString(2, user.getPassword());
            result = authenticateUser.executeQuery();
            while (result.next()) {
                authenticated = true;
            }
        } catch (SQLException e) {
            authenticated = false;
        }
        return authenticated;
    }

    public boolean checkIfUserExists(User user) {
        boolean exists = false;
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement checkIfUserExists = null;
        try {
            checkIfUserExists = conn.prepareStatement("select * from user where username=?");
            checkIfUserExists.setString(1, user.getUsername());
            result = checkIfUserExists.executeQuery();
            while (result.next()) {
                exists = true;
            }
        } catch (SQLException e) {
            exists = false;
        }
        return exists;
    }

    public User getUserByUsername(String username) {
        User user = new User();
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement selectUserByUsername = null;
        try {
            selectUserByUsername = conn.prepareStatement("select * from user where username=?");
            selectUserByUsername.setString(1, username);
            result = selectUserByUsername.executeQuery();
            while (result.next()) {
                user = getUser(result);
            }
        } catch (SQLException e) {
            e.printStackTrace(System.out);
        }
        return user;
    }

    public User getUserById(int userId) {
        User user = new User();
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement selectUserById = null;
        try {
            selectUserById = conn.prepareStatement("select * from user where id=?");
            selectUserById.setInt(1, userId);
            result = selectUserById.executeQuery();
            while (result.next()) {
                user = getUser(result);
            }
        } catch (SQLException e) {
            e.printStackTrace(System.out);
        }
        return user;
    }

    public boolean deleteUser(int userId) {
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement deleteUser = null;
        try {
            deleteUser = conn.prepareStatement("delete from user where userid=?");
            deleteUser.setInt(1, userId);
            int deleteCount = deleteUser.executeUpdate();
            if (deleteCount > 0) {
                return true;
            } else {
                return false;
            }

        } catch (SQLException e) {
            throw new EStoreException(e);

        }

    }

    public User getUser(ResultSet result) {
        User user = new User();
        try {
            user = new User();
            user.setId(result.getInt("id"));
            user.setUsername(result.getString("username"));
            user.setPassword(result.getString("password"));
            user.setUserTypeId(result.getInt("usertypeid"));
            user.setStaffId(result.getInt("staffid"));
        } catch (SQLException e) {
            e.printStackTrace(System.out);
        }
        return user;
    }

    public List<User> getAllUsers(int start, int size) {
        List<User> users = new ArrayList<User>();
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement selectAllUsers = null;
        try {
            selectAllUsers = conn.prepareStatement("select * from user LIMIT ?,?");
            selectAllUsers.setInt(1, start);
            selectAllUsers.setInt(2, size);
            result = selectAllUsers.executeQuery();
            while (result.next()) {
                users.add(getUser(result));
            }
        } catch (SQLException e) {
            e.printStackTrace(System.out);
        }
        return users;
    }

    public int getAllUsersCount() {
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement selectAllUsersCount = null;
        try {
            selectAllUsersCount = conn.prepareStatement("select count(*) from user");
            result = selectAllUsersCount.executeQuery();
            while (result.next()) {
                return result.getInt(1);
            }
        } catch (SQLException e) {
            throw new EStoreException(e);
        }
        return 0;

    }
}
